/*
 
 TopMind jLynx
 Copyright (c) 2004-onwards. TopMind Systems LLC.
 All rights reserved.
 
 This file is part of TopMind jLynx.
 
 TopMind jLynx is free software; you can redistribute it and/or modify
 it under the terms of the License. See website for License.
 
 */
package org.rishi.framework.db;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Implementation of DAO interface.
 *
 * @noinspection UnusedDeclaration
 * @see org.rishi.framework.db.DAO
 */
public class DAOImpl implements DAO {

    private static final Logger logger = LoggerFactory.getLogger(DAOImpl.class);

    static final Map connMap = new TreeMap();

    static final Map entityMap = new TreeMap();

    private static final Map pkMap = new TreeMap();

    private static final List removeProp;

    private static final long serialVersionUID = 1L;
    
    
    // private member fields
    private boolean autoCommit = true;
    private Object bean;
    private Connection conn;
    private String dsName;
    private int dbVendor;
    private final boolean domapping = false;
    private String entityName;
    private boolean initialized = false;
    private boolean keepNullsInQuery = false;
    private String[] keys;
    private PreparedStatement ps;
    private ResultSet rs;
    private Statement stmt;

    static {

        // TODO make this XML-configurable
        removeProp = new ArrayList();
        removeProp.add("class");
        removeProp.add("multipartRequestHandler");
        removeProp.add("servletWrapper");
        removeProp.add("entity");
        removeProp.add("bean");
        removeProp.add("validConfig");
        removeProp.add("page");
        removeProp.add("resultValueMap");
        removeProp.add("validatorResults");

        ConfigParser cp = new ConfigParser();
        logger.info(cp + " :: connections configured: " + connMap.keySet());
        Iterator iter = entityMap.keySet().iterator();
        while (iter.hasNext()) {
            Object o = iter.next();
            logger.info("class/entity pairing :: " + o + " maps to "
                    + entityMap.get(o));
        }
    }

    private static String fixNulls(String sqlIn) {
        return StringUtils.replace(sqlIn, "'null'", "NULL");
    }

    /**
     * pre-condition: ResultSet next() called
     *
     * @param rs     - resultset
     * @param object - pojo
     * @return pojo
     * @throws Exception
     */
    private static Object setValues(ResultSet rs, Object object)
            throws Exception {

        if (object == null)
            return null;

        try {

            int colCount = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= colCount; i++) {

                String colName = rs.getMetaData().getColumnName(i);
                Object value;
                int type = rs.getMetaData().getColumnType(i);
                logger.trace("Type=" + type);
                if (type == Types.BLOB || type == Types.BINARY
                        || type == Types.LONGVARBINARY) {
                    value = rs.getBlob(i);
                    if (value != null) {
                        value = ((Blob) value).getBinaryStream();
                        logger.debug("Object is a BLOB!");
                    }
                } else if (type == Types.VARCHAR || type == Types.CLOB
                        || type == Types.LONGVARCHAR) {
                    value = rs.getString(i);
                } else
                    value = rs.getObject(i);

                if (object instanceof Map) {

                    ((Map) object).put(colName, value);

                } else {

                    BeanUtils.setValue(colName, object, value);

                }

            }// end for

        } catch (SQLException e) {
            logger.error("Oops! Database error: " + e.getMessage());
            throw e;
        }
        return object;
    }



    /**
     * Create a <code>net.sf.jlynx.DAO</code> instance using an empty
     * <code>Map</code> ; cannot be used for CRUD operations.
     */
    public DAOImpl() {
        setEntity("CRUD_OPERATIONS_NOT_SUPPORTED");
        refresh(new HashMap());
        logger.debug("CRUD operations not supported");
    }

    /**
     * Create a <code>net.sf.jlynx.DAO</code> instance using provided Class;
     * cannot be used for CRUD operations.
     *
     * @param clazz - Class object
     */
    public DAOImpl(Class clazz) {
        setEntity(clazz);
        try {
            refresh(clazz.newInstance());
        } catch (InstantiationException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * Create a <code>net.sf.jlynx.DAO</code> instance using provided POJO.
     *
     * @param pojo - a POJO
     */
    public DAOImpl(Serializable pojo) {

        // logger.trace("Simple name = " + obj.getClass().getSimpleName());
        setEntity(pojo.getClass());
        refresh(pojo);

        // if (!(obj instanceof Map)) {
        logger.debug("Creating instance of " + DAO.class.getName()
                + " :: using object of type: " + pojo.getClass().getName());
        logger.debug("Current DB entity is: '" + getEntity()
                + "' :: Switch entity using #setEntity()");
        // }

    }

    public void commit() {
        if (conn != null) {
            try {
                conn.commit();
                this.autoCommit = true;
                release();
            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            } finally {
                this.autoCommit = true;
                conn = null;
            }
        } else
            logger.debug("Connection already closed!");
    }

    final void connect() throws SQLException {

        logger.trace("#connect() " + initialized);
        if (dsName == null && conn == null)
            setConnection("default");

        if (!(dsName == null) && (conn == null))
            connectDS();

        if (conn != null) {
            this.dbVendor = SchemaUtil.findDbVendor(conn.getMetaData());
            conn.setAutoCommit(autoCommit);
        } else {
            throw new SQLException("Connection not configured!");
        }

    }

    private void connectDS() throws SQLException {

        logger.trace("#connectDS() " + dsName);

        Context ctx = null;
        DataSource ds = null;
        try {
            ctx = new InitialContext();
            ds = (DataSource) ctx.lookup("java:comp/env/" + this.dsName);
            if (ds == null)
                throw new NamingException();

        } catch (NamingException e) {
            try {
                if (ctx != null) {
                    ds = (DataSource) ctx.lookup(this.dsName);
                }
            } catch (NamingException e1) {
                // e1.printStackTrace();
                logger.warn(e.getMessage());
            }
        } finally {
            if (ds != null) {
                conn = ds.getConnection();
                conn.setAutoCommit(autoCommit);
            } else
                throw new SQLException("Could not lookup connection by name: " + dsName);
        }

        // return this;
    }

    private String createFilterStmt() throws
            SQLException {

        String sql = " WHERE ";
        String delimiter;

        Object obj = this.bean;
        if (keys == null)
            initPK();

        if (keys != null) {
            int keySize = keys.length;
            for (int i = 0; i < keySize; i++) {
                if (i != 0) {
                    sql += " AND ";
                }
                sql += keys[i];

                // String keyValue = "";
                Object partKeyValue;

                partKeyValue = BeanUtils.getValue(keys[i], obj);
                if (partKeyValue == null && !(bean instanceof Map))
                    throw new SQLException("Primary Key value is missing! PK="
                            + keys[i]);
                else if (partKeyValue == null) {
                    partKeyValue = ((Map) bean).get(keys[i]);
                    if (partKeyValue == null)
                        throw new SQLException(
                                "Map missing Primary Key! Map case-sensitive! PK="
                                        + keys[i]);
                }

                // DB2 fix as above
                if (DataTypeMappings.isNumber(partKeyValue))
                    delimiter = "";
                else
                    delimiter = "'";

                sql += "=" + delimiter + partKeyValue.toString() + delimiter;
            }
        }
        return sql;
    }

    private String createInsertStmt() {

        StringBuffer sql = new StringBuffer();

        Map props;
        String delimiter;

        Object obj = bean;

        if (obj instanceof Map) {
            // ((Map) obj).remove((FormHandler.PK_MAP_KEY));
            props = (Map) obj;
        } else
            props = BeanUtils.describe(obj);

        // remove useless properties from this object
        Iterator iter = removeProp.iterator();
        while (iter.hasNext())
            props.remove(iter.next());

        // fix to account for identity fields in SQL Server and other
        // default values
        removeNulls(props);

        Iterator i = props.keySet().iterator();
        String fld;
        String[] fields = new String[props.size()];
        int j = 0;
        while (i.hasNext()) {
            fld = (String) i.next();

            fields[j] = fld;
            j++;
            // logger.debug(fld);
            sql.append(fld);
            if (i.hasNext()) {
                sql.append(",");
            } else {
                // fix for configured column mappings
                String end = ",$END-OF-COLUMN65$";
                sql.append(end);
                // logger.debug(sql);
                if (domapping)
                    sql = new StringBuffer(sql.toString());
                // logger.debug(sql);
                sql = new StringBuffer(StringUtils.replace(sql.toString(), end,
                        ""));
                sql.append(") VALUES (");
            }
        }

        i = props.values().iterator();
        j = 0;
        while (i.hasNext()) {

            Object val = i.next();
            // logger.debug(val);
            String oracleDate1 = "";
            String oracleDate2 = "";

            if (dbVendor == ORACLE) {

                // Oracle fix for Dates
                if (java.sql.Timestamp.class.equals(BeanUtils.getType(
                        fields[j], obj))) {
                    // System.out.println(fld);
                    oracleDate1 = "to_date(";
                    oracleDate2 = ",'yyyy-mm-dd hh24:mi:ss\".999\"')";

                }
            }

            if (dbVendor == MSSQL) {

                // MSSQL fix for Bits/Boolean
                Class cls = BeanUtils.getType(fields[j], obj);

                if (Boolean.class.equals(cls)) {

                    Boolean valB = (Boolean) val;

                    if (valB.booleanValue())
                        val = "1";
                    else
                        val = "0";

                    logger.trace("SQL bit fix :: " + cls);

                } else if (cls == boolean.class) {
                    // boolean bit = ((Boolean) val).booleanValue();
                    if ("true".equals(val.toString()))
                        val = "1";
                    else
                        val = "0";

                    logger.trace("SQL bit fix :: " + cls);
                }

            }

            Object field;

            // field = PropertyUtils.getProperty(obj, fields[j]);
            field = BeanUtils.getValue(fields[j], obj);

            if (DataTypeMappings.isNumber(field)) {
                delimiter = "";
            } else
                delimiter = "'";

            j++;

            sql.append(oracleDate1).append(delimiter).append(StringUtils.escapeQuotes(val.toString())).append(delimiter).append(oracleDate2);
            if (i.hasNext()) {
                sql.append(",");
            } else {
                sql.append(")");
            }
        }

        String result = "INSERT INTO " + entityName + " ("
                + fixNulls(sql.toString());

        logger.debug("INSERT stmt: " + result);

        return result;
    }

    private String createSelectStmt() throws NoSuchMethodException,
            SQLException {

        String sql = "SELECT * FROM " + entityName + createFilterStmt();
        logger.debug("SELECT SQL statement: " + sql);
        return sql;
    }

    private String createUpdateStmt() throws Exception {

        String delimiter;

        String sql = "UPDATE " + entityName + " SET ";

        String where = createFilterStmt();

        logger.trace("WHERE clause: " + where);

        Map map = new TreeMap();

        // if (bean instanceof Map) {
        // map.putAll((Map) this.bean);
        // logger.trace("Map " + map);
        // } else
        map.putAll(BeanUtils.describe(bean));

        // remove useless properties from this object
        Iterator iter = removeProp.iterator();
        while (iter.hasNext())
            map.remove(iter.next());

        // remove null values from the bean
        removeNulls(map);

        Iterator i = map.keySet().iterator();
        int j = 0;
        while (i.hasNext()) {

            j++;
            String colName = (String) i.next();
            String value = null;
            boolean isMap = bean instanceof Map;
            Object o;
            o = map.get(colName);

            if (o != null) {
                value = StringUtils.escapeQuotes(o.toString());
                // if (this.keyField != null) {
                // if (colName.equalsIgnoreCase(keyField))
                // value = null;
                // } else

                if (this.keys != null) {
                    for (int k = 0; k < keys.length; k++) {
                        if (colName.equalsIgnoreCase(keys[k]))
                            value = null;
                    }
                } else
                    throw new SQLException(
                            "Primary key(s) is not set for the UPDATE query");// no
                // primary
                // key
            }

            if (value != null) {

                String oracleDate1 = "";
                String oracleDate2 = "";

                if (!isMap && dbVendor == ORACLE) {

                    // Oracle fix for Dates
                    Class cls = BeanUtils.getType(colName, bean);
                    if (java.sql.Timestamp.class.equals(cls)) {
                        // System.out.println("Oracle Date");
                        oracleDate1 = "to_date(";
                        oracleDate2 = ",'yyyy-mm-dd hh24:mi:ss\".999\"')";

                    }
                }

                if (!isMap && dbVendor == MSSQL) {

                    Class cls = BeanUtils.getType(colName, bean);

                    // MSSQL fix for Bits/Boolean
                    if (Boolean.class.equals(cls)) {
                        Boolean valB = (Boolean) BeanUtils.getValue(colName,
                                bean);

                        logger.trace("SQL Server boolean/bit! value=" + valB);

                        if (valB == null)
                            value = null;
                        else if (valB.booleanValue())
                            value = "1";
                        else
                            value = "0";

                        // logger.debug("SQL bit fix :: " + cls);

                    } else if (cls == boolean.class) {

                        if ("true".equals(value))
                            value = "1";
                        else
                            value = "0";

                        logger.trace("SQL bit fix :: " + cls);
                    }
                }

                // do fix here for DB2 numeric types
                if (!isMap
                        && DataTypeMappings.isNumber(BeanUtils.getValue(
                        colName, bean))) {
                    delimiter = "";
                } else
                    delimiter = "'";

                // logger.debug("Bean:Column pairing: " + colName + "::" +
                // fixUpdateMappings(colName));

                // colName = fixUpdateMappings(colName);

                sql += colName + "=" + oracleDate1 + delimiter + value
                        + delimiter + oracleDate2 + ", ";
                // logger.debug(sql);
            }

        }
        if (j == 0) {
            return null;
        }

        sql = sql.substring(0, sql.length() - 2) + where;

        sql = fixNulls(sql);

        logger.debug("UPDATE stmt: " + sql);

        return sql;
    }

    Connection defaultConnection() throws SQLException {
        connect();
        return conn;
    }

    public final boolean delete() throws SQLException {

        if (bean instanceof Map)
            throw new UnsupportedOperationException();

        connect();
        String sql = null;

        try {
            sql = "DELETE FROM " + entityName + createFilterStmt();

            // logger.debug(sql);
            stmt = conn.createStatement();
            int result = stmt.executeUpdate(sql);
            return result == 1;
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
            if (e instanceof SQLException) {
                throw (SQLException) e;
            } else
                throw new SQLException(e.getMessage());
        } finally {
            logger.debug(sql);
            release();
        }
    }

    public boolean exec(String query, Object[] p) throws SQLException {

        String sql = (ConfigParser.getQuery(query) == null || ""
                .equals(ConfigParser.getQuery(query))) ? query : ConfigParser
                .getQuery(query);

        // if (this.queries != null && queries.containsKey(query)) {
        // sql = queries.get(query).toString();
        // }

        logger.debug("SQL stmt = " + sql);

        boolean result;
        connect();

        ps = conn.prepareStatement(sql);
        logger.trace("conn = " + conn + ":" + autoCommit);

        setParams(p);
        result = ps.execute();
        if (this.autoCommit)
            release();
        logger.trace("Exiting");
        return result;

    }

    private List executeQuery() throws SQLException {

        List result = new ArrayList();

        if (ps != null)
            rs = ps.executeQuery();

        while (rs != null && rs.next()) {

            try {

                Object obj;
                if (bean instanceof Class)
                    obj = ((Class) bean).newInstance();
                else
                    obj = bean.getClass().newInstance();
                obj = setValues(rs, obj);
                result.add(obj);

            } catch (Exception e) {
                logger.info("Problem setting values from ResultSet :: " + bean);
            }
        }

        release();
        return result;

    }

    public String fetchJSON(String query, Object[] params)
            throws SQLException {

        List list = fetchList(query, params);
        return toJSON(list);

    }

    public List fetchList(String query, Object[] p) throws SQLException {

        connect();

        String sql = (ConfigParser.getQuery(query) == null || ""
                .equals(ConfigParser.getQuery(query))) ? query : ConfigParser
                .getQuery(query);

        logger.debug("SQL stmt = " + sql);

        this.ps = this.conn.prepareStatement(sql);

        setParams(p);
        return executeQuery();
    }

    public String fetchXML(String namedQuery, Object[] params,
                           String elementName) throws SQLException {
        List list = this.fetchList(namedQuery, params);
        return BeanUtils.toXml(list, elementName);
    }

    public void generateCode(String p, String s) {
        try {
            connect();
            DBGenerator.generateCode(p, s, null, null, conn);
            logger.info("Check " + System.getProperty("user.home")
                    + " folder for source code");
        } catch (Exception e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        }
    }

    // Returns the entityName.
    private String getEntity() {
        return entityName;
    }

    // initialize; setup primary keys
    private void initPK() throws SQLException {

        initialized = false;
        // keyField = null;
        keys = null;
        logger.trace(conn + " entity=" + getEntity());

        if (conn != null && getEntity() != null) {

            try {

                String url = conn.getMetaData().getURL();
                String pkMapKey = url + "|" + getEntity();
                List pks = (List) pkMap.get(pkMapKey);

                if (pks == null) {

                    ResultSet r = conn.getMetaData().getPrimaryKeys(null, null,
                            getEntity());

                    pks = new ArrayList();
                    Set pkSet = new HashSet();
                    String pk;
                    while (r.next()) {
                        pk = r.getString(4);
                        pkSet.add(pk);
                    }
                    pkSet.remove("");
                    pkSet.remove(null);
                    if (!pkSet.isEmpty()) {
                        Iterator iter = pkSet.iterator();
                        while (iter.hasNext())
                            pks.add(iter.next());
                        pkMap.put(pkMapKey, pks);
                    } else {
                        logger.warn("Primary key for " + getEntity()
                                + " was not found");
                    }
                }

                // bug! v1.4.2 Object[] cannot be cast to String[]
                // this.keyFields = (String[]) pks.toArray();
                keys = new String[pks.size()];
                for (int i = 0; i < pks.size(); i++) {
                    keys[i] = pks.get(i).toString();
                }

                setAutoCommit(conn.getAutoCommit());
                logger.trace("Primary key map... " + pkMap);
                // logger.warn("Primary key map... " + keys);

            } catch (SQLException e) {
                initialized = false;
                throw e;
            }

        }

        // exit if bean is a Map and setEntity() has not been called yet
        if (bean == null || (bean instanceof Map && getEntity() == null))
            return;

        logger.trace("bean = " + bean);
        logger.trace("entity = " + getEntity() + " object class = "
                + bean.getClass());

        initialized = true;
    }

    /*
      * (non-Javadoc)
      *
      * @see biz.topmind.jlynx.Relational#insert()
      */
    public final int insert() throws SQLException {

        if (bean instanceof Map)
            throw new UnsupportedOperationException();

        connect();

        int result;
        try {
            String sql = createInsertStmt();
            stmt = conn.createStatement();
            logger.debug(sql);
            result = stmt.executeUpdate(sql);
            if (dbVendor == MSSQL && result == 1) {
                try {
                    int ident = 1;
                    String identitySql = "SELECT SCOPE_IDENTITY()";

                    rs = stmt.executeQuery(identitySql);
                    if (rs.next())
                        ident = rs.getInt(1);

                    logger
                            .debug(dbVendor + " : " + identitySql + " = "
                                    + ident);

                    result = ident;
                } catch (Exception e) {
                    result = 1;
                }
            } else if (dbVendor == MYSQL && result == 1) {

                // use mysql last_insert_id() to return the auto_increment value
                // if it returns 0, return 1 instead

                String ident = "";

                rs = stmt.executeQuery("select LAST_INSERT_ID() as ident");
                if (rs.next())
                    ident = rs.getString(1);

                logger.trace("mysql LAST_INSERT_ID() = " + ident);

                try {
                    result = Integer.parseInt(ident);
                    if (result == 0)
                        result = 1;
                } catch (NumberFormatException e) {
                    result = 1;
                }
            }

        } catch (Exception e) {
            logger.error(e.getMessage());
            // e.printStackTrace();
            if (e instanceof SQLException) {
                throw (SQLException) e;
            } else
                throw new SQLException(e.getMessage());
        } finally {
            release();
        }
        return result;
    }

    private void refresh(Object object) {

        if (bean == null) {
            bean = object;
            return;
        }

        if (!BeanUtils.describe(object).keySet().equals(
                BeanUtils.describe(bean).keySet())) {
            logger.debug("Attempt to refresh bean failed");
        } else {
            this.bean = object;
        }

    }

    private void release() throws SQLException {

        logger.trace("AutoCommit = " + this.autoCommit);

        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (ps != null) {
                ps.close();
                ps = null;
            }
        } catch (SQLException e) {
            logger.error(e.getMessage());
        } finally {

            logger.trace("conn=" + conn);

            if (conn != null && autoCommit) {
                conn.close();
                conn = null;
            }
        }
        logger.trace("conn=" + conn);
    }

    private void removeNulls(Map map) {

        logger.debug("Keep nulls in SQL query :: " + this.keepNullsInQuery);

        List keysToRemove = new ArrayList();

        Iterator iter = map.keySet().iterator();
        while (iter.hasNext()) {

            String key = (String) iter.next();

            if (keepNullsInQuery) {

                // for empty Strings put null
                if ("".equals(map.get(key)) || map.get(key) == null) {
                    // logger.debug(key + ":" + map.get(key));
                    map.put(key, "null");
                }

            } else {

                if (map.get(key) == null) {
                    // logger.debug(key + "=" + map.get(key));
                    // remove these in a safe manner 9/12/06
                    keysToRemove.add(key);
                }
            }
        }
        // remove keys from map
        iter = keysToRemove.iterator();
        while (iter.hasNext()) {
            map.remove(iter.next());
        }

    }

    public void rollback() {

        if (conn != null) {

            try {

                conn.rollback();
                this.autoCommit = true;
                release();

            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            } finally {
                this.autoCommit = true;
            }

        } else
            logger.debug("Connection already closed!");

    }

    /*
      * (non-Javadoc)
      *
      * @see biz.topmind.jlynx.Relational#save()
      */
    public int save() throws SQLException {

        logger.trace("Entering save()");
        if (bean instanceof Map)
            throw new UnsupportedOperationException();

        connect();

        int result = 0;

        if (!autoCommit) {
            throw new SQLException(
                    "save() not supported in transaction, use insert() or update()!");
        } else {

            String where;
            try {
                where = createFilterStmt();
            } catch (Exception e1) {
                // e1.printStackTrace();
                logger.debug("Primary key value not found! Trying insert()");
                result = insert();
                return result;
            }

            setAutoCommit(false);
            try {

                // 1. find out if object exists in DB first
                // 2. if(exists) update else insert

                String select = "SELECT ";
                select += keys[0];

                select += " FROM " + getEntity() + where;

                logger
                        .debug("Does database record exist? test qry = "
                                + select);

                stmt = conn.createStatement();
                ResultSet resultset = stmt.executeQuery(select);
                boolean doUpdate = resultset.next();
                resultset.close();
                // setAutoCommit(true);
                if (doUpdate) {
                    logger.debug("Yes. Perform update()");
                    result = update();
                } else {
                    logger.debug("Not found. Perform insert()");
                    result = insert();
                }

            } catch (Exception e) {
                result = 0;
                logger.error(e.getMessage());
                // if (e instanceof SQLException)
                throw new SQLException(e.getMessage());
            } finally {
                setAutoCommit(true);
                release();
            }

        }
        logger.trace("Exiting save()");
        return result;
    }

    public void saveNulls(boolean updateNulls) {
        this.keepNullsInQuery = updateNulls;
    }

    public final boolean select() throws SQLException {

        if (bean instanceof Map)
            throw new UnsupportedOperationException();

        connect();

        String sql;

        try {
            // connect();
            boolean result;
            sql = createSelectStmt();

            stmt = conn.createStatement();
            ResultSet resultset = stmt.executeQuery(sql);

            if ((result = resultset.next())) {
                if (bean instanceof Map) {
                    setSingleRowValues(resultset);
                    logger.debug("Map " + bean);
                } else
                    this.bean = setValues(resultset, this.bean);
            }

            logger.debug("SELECT stmt: " + sql + "::" + result);

            return result;

        } catch (Exception e) {
            // logger.error(sql);
            logger.error(e.getMessage());
            e.printStackTrace();
            if (e instanceof SQLException) {
                throw (SQLException) e;
            } else
                throw new SQLException(e.getMessage());

        } finally {

            release();

        }
    }

    public DAO setAutoCommit(boolean b) {

        if (conn != null) {
            try {
                this.conn.setAutoCommit(b);
                this.autoCommit = b;
            } catch (SQLException e) {
                logger.error("Problem with autoCommit!");
                e.printStackTrace();
            }
        } else {
            this.autoCommit = b;
        }

        return this;
    }

    public DAO setConnection(Object cn) {
        logger.trace("#setConnection " + cn);
        if (cn instanceof Connection)
            this.conn = (Connection) cn;
        else {
            // check connMap for connection
            if (cn instanceof String) {
                Object cfgConn = connMap.get(cn);

                if (cfgConn == null) {
                    dsName = cn.toString();
                } else {
                    if (cfgConn instanceof List) {
                        try {
                            List cfgList = (List) cfgConn;
                            Class.forName(cfgList.get(0).toString());
                            // TODO check for NPE
                            if (cfgList.get(2) == null) {
                                conn = DriverManager.getConnection(cfgList.get(
                                        1).toString());
                                logger.debug("No user configured!");
                            } else
                                conn = DriverManager.getConnection(cfgList.get(
                                        1).toString(), cfgList.get(2)
                                        .toString(), cfgList.get(3).toString());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    } else
                        dsName = cfgConn.toString();
                }

            }
        }
        return this;
    }

    private void setEntity(Class clazz) {
        logger.trace("Class name = " + clazz.getName());
        if (DAOImpl.entityMap.containsKey(clazz.getName())) {
            this.entityName = (String) DAOImpl.entityMap.get(clazz.getName());
        } else {
            String clsName = clazz.getName().toUpperCase();
            String pkgName = (clazz.getPackage() == null) ? "" : clazz
                    .getPackage().getName().toUpperCase()
                    + ".";

            this.entityName = clsName.substring(pkgName.length());
        }
        logger.debug("Entity set as: " + this.entityName);
    }

    public DAO setEntity(String entity) {
        this.entityName = entity;
        return this;
    }

    private void setParams(Object[] params) throws SQLException {
        if (params != null && params.length > 0) {
            // int i = 0;
            for (int i = 0; i < params.length;) {
                Object o = params[i];
                if (o instanceof Integer) {
                    ps.setInt(++i, ((Integer) o).intValue());
                } else if (o instanceof InputStream) {
                    try {
                        logger.trace("Setting BLOB param");
                        ps.setBinaryStream(++i, (InputStream) o,
                                ((InputStream) o).available());

                    } catch (IOException e) {
                        e.printStackTrace();
                        throw new SQLException(
                                "Error setting BinaryStream value in PreparedStatement");
                    }
                } else
                    ps.setObject(++i, o);
            }
        }
    }

    private void setSingleRowValues(ResultSet rs) throws Exception {
        if (bean != null && bean instanceof Map) {

            ((Map) bean).clear();

            // assumes a single record
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                ((Map) bean).put(rs.getMetaData().getColumnName(i), rs
                        .getObject(i));
            }
        }
    }

    public String toJSON() {
        return BeanUtils.toJSON(bean);
    }

    // public String toJavascript( String formName ) {
    // return BeanUtils.toJSFormBind(formName, bean);
    // }

    public String toJSON(List list) {
        StringBuffer json = new StringBuffer(
                "{\n  \"Info\": \"jlynx Relational#jsonArray() generated\",\n  \"QueryTime\": \"");
        json.append(new Timestamp(System.currentTimeMillis())).append("\",");
        json.append("\n  \"QueryResults\": {\n");
        // List<?> list = select(query, params);
        json.append("    \"ResultSize\": ").append(list.size()).append(",\n");

        if (list.size() == 0)
            return json.toString() + "    \"Results\": null\n}\n}";

        boolean addClass = true;
        for (int i = 0; i < list.size(); i++) {
            Object obj = list.get(i);
            if (addClass) {
                json.append("    \"ResultClass\": \"").append(obj.getClass().getName()).append("\",\n    \"Results\": [");
                addClass = false;
            }
            String j = BeanUtils.toJSON(obj);
            // int start = j.indexOf("{", 1);
            json.append(j).append(",\n");
        }
        String result = json.toString().substring(0, json.length() - 2);
        return result + "]}}";
    }

    public String toXml() {
        return toXml(null);
    }

    public String toXml(String element) {
        logger.debug("Entering method toXml");
        return BeanUtils.toXml(bean, element);
    }

    public final int update() throws SQLException {

        if (bean instanceof Map)
            throw new UnsupportedOperationException();

        connect();

        String sql;
        try {
            int result;
            sql = createUpdateStmt();

            stmt = conn.createStatement();

            logger.debug(sql);

            result = stmt.executeUpdate(sql);
            return result;

        } catch (Exception e) {
            logger.error(e.getMessage());
            if (e instanceof SQLException) {
                throw (SQLException) e;
            } else
                throw new SQLException(e.getMessage());
        } finally {
            release();
        }
    }

}// end class
